# -*- coding:utf-8 -*-
from com.zzh.utils.dbUtils import MysqlUtils
from com.zzh.utils.table2Json_v2 import Table2JsonByYM
from com.zzh.utils.pagingUtils import MyPaging
import sys
default_encoding = 'utf-8'
if sys.getdefaultencoding() != default_encoding:
    reload(sys)
    sys.setdefaultencoding(default_encoding)

from com.zzh.utils.sqlUtils.SqlUtils import SqlUtils
from com.zzh.utils.requestFieldMapDBField import requestFieldMapToDBField_YM


import time

localTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))

"""
        表名：zzh_service


"""


class ServiceDao(object):
    def __init__(self):
        self.conn = MysqlUtils.get_mysql_connections_v2()

    def selectService(self, id):
        """
        根据ID查询信息，返回元组

        """
        sql = "select * from zzh_service WHERE service_id=%s"

        cur = self.conn.cursor()
        rs = cur.execute(sql, [id])
        arr = cur.fetchone()
        cur.close()
        self.conn.close()
        return arr

    def queryServiceById(self, id):
        """
        根据ID查询信息    查询
        :param :
        :return:
        单体查询，按照id查询
        """
        sql = "SELECT * FROM zzh_service WHERE service_id=%s"
        cur = self.conn.cursor()
        total_count = cur.execute(sql, [id])
        if total_count:
            result = cur.fetchall()
            cur.close()
            self.conn.close()
            userOauthJson = Table2JsonByYM.service_list_to_json(result)
            return total_count, userOauthJson
        else:
            cur.close()
            self.conn.close()
            return 0, None

    def queryServiceByCondition(self, **kwargs):
        """
        条件查询
        :param :
        :return:
        此处使用userId为条件查询
        """

        """

        page_index = kwargs['pageIndex']
        PAGE_SIZE = kwargs['pageSize']

        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']

        fields = []
        ops = []
        values = []
        for con in conditions:
            fields.append(con['field'])
            ops.append(con['op'])
            values.append(con['value'])
        sortFields = []
        sorts = []
        for sort in multiSort:
            sortFields.append(sort['field'])
            sorts.append(sort['sort'])

        field = []
        for i in fields:
            field.append(requestFieldMapToDBField.serviceRequestFieldMap[i])

        option = SqlUtils.getOption(ops)
        whereSentence = SqlUtils.getWhereSentence(field, option, values)

        sortField = []

        for i in sortFields:
            sortField.append(requestFieldMapToDBField.serviceRequestFieldMap[i])
        sortRule = SqlUtils.getSortRule(sortField, sorts)

        cur = self.conn.cursor()
        sql = "SELECT * FROM zzh_service WHERE " + whereSentence + " ORDER BY " + sortRule

        total_count = cur.execute(sql)

        if total_count:
            result = cur.fetchall()
            cur.close()
            self.conn.close()
            if (page_index * PAGE_SIZE) < total_count:
                start = (page_index - 1) * PAGE_SIZE
                end = page_index * PAGE_SIZE
                userOauthPart = result[start: end]
                userOauthJSON = Table2JsonByYM.service_list_to_json(userOauthPart)
                return total_count, userOauthJSON
            else:
                start = (page_index - 1) * PAGE_SIZE
                userOauthPart = result[start:]
                userOauthJSON = Table2JsonByYM.service_list_to_json(userOauthPart)
                return total_count, userOauthJSON

        else:
            cur.close()
            self.conn.close()
            return None
        """

        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']
        pageIndex = kwargs['pageIndex']
        pageSize = kwargs['pageSize']



        fieldMap = requestFieldMapToDBField_YM.serviceRequestFieldMap#要改的这个
        whereSentence = SqlUtils.getOption(conditions, fieldMap)

        cur = self.conn.cursor()
        if multiSort:
            # 排序拼接
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "SELECT * FROM zzh_service WHERE " + whereSentence + " ORDER BY " + sortRule
        else:
            sql = "SELECT * FROM zzh_service WHERE " + whereSentence
        rs = cur.execute(sql)

        total_count = cur.execute(sql)
        if total_count:
            result = cur.fetchall()

            cur.close()
            self.conn.close()
            resultList= MyPaging().getPage(total_count,pageIndex,pageSize,result)
            userOauthJSON = Table2JsonByYM.service_list_to_json(resultList)
            return userOauthJSON,total_count
        else:

            cur.close()
            self.conn.close()
            return None,0

    def insertService(self, Info):
        """

        :param userRechargeInfo:
        :return:
        """

        serviceName = Info['serviceName']  #
        serviceCode = Info['serviceCode']
        servicePrice = Info['servicePrice']  #
        serviceRate = Info['serviceRate']
        serviceStatus = Info['serviceStatus']
        serviceContent = Info['serviceContent']  #
        serviceContact = Info['serviceContact']  #
        serviceStartDate = Info['serviceStartDate']  #
        serviceEndDate = Info['serviceEndDate']  #

        #




        cur = self.conn.cursor()
        sql = 'INSERT INTO zzh_service (service_name, service_code, service_price, service_rate, service_status, service_content,service_contract,service_startdate,service_enddate) VALUES (%s, %s ,%s ,%s, %s, %s, %s,%s,%s)'
        rs = cur.execute(sql, [serviceName, serviceCode, servicePrice, serviceRate, serviceStatus, serviceContent,
                               serviceContact, serviceStartDate, serviceEndDate])
        self.conn.commit()

        sql2 = 'SELECT max(service_id) FROM zzh_service'
        rs2TC = cur.execute(sql2)
        rs2 = cur.fetchone()
        rs2Int = rs2[0]

        sql3 = "SELECT * FROM zzh_service WHERE service_id=%s"
        cur = self.conn.cursor()
        total_count = cur.execute(sql3, [rs2Int])
        if total_count:
            result = cur.fetchall()
            cur.close()
            self.conn.close()
            userOauthJson = Table2JsonByYM.service_list_to_json(result)
            return total_count, userOauthJson
        else:
            cur.close()
            self.conn.close()
            return 0, None

    def insertServiceByItem(self, Info):
            """

            :param userRechargeInfo:
            :return:
            """

            serviceName = Info['serviceName']  #
            serviceCode = Info['serviceCode']
            servicePrice = Info['servicePrice']  #
            serviceRate = Info['serviceRate']
            serviceStatus = Info['serviceStatus']
            serviceContent = Info['serviceContent']  #
            serviceContact = Info['serviceContact']  #
            serviceStartDate = Info['serviceStartDate']  #
            serviceEndDate = Info['serviceEndDate']  #
            itemId = Info['itemId']

            #




            cur = self.conn.cursor()
            sql = 'INSERT INTO zzh_service (service_name, service_code, service_price, service_rate, service_status, service_content,service_contract,service_startdate,service_enddate) VALUES (%s, %s ,%s ,%s, %s, %s, %s,%s,%s)'
            rs = cur.execute(sql, [serviceName, serviceCode, servicePrice, serviceRate, serviceStatus, serviceContent,
                                   serviceContact, serviceStartDate, serviceEndDate])
            self.conn.commit()




            sql2 = 'SELECT max(service_id) FROM zzh_service'
            rs2TC = cur.execute(sql2)
            rs2 = cur.fetchone()
            rs2Int = rs2[0]

            curItemId = self.conn.cursor()
            sqlcurItemId = "UPDATE zzh_item SET service_id =%s WHERE item_id = %s "

            rscurItemId = curItemId.execute(sqlcurItemId, [rs2Int, itemId])
            self.conn.commit()



            sql3 = "SELECT * FROM zzh_service WHERE service_id=%s"
            cur = self.conn.cursor()
            total_count = cur.execute(sql3, [rs2Int])
            if total_count:
                result = cur.fetchall()
                cur.close()
                self.conn.close()
                userOauthJson = Table2JsonByYM.service_list_to_json(result)
                return total_count, userOauthJson
            else:
                cur.close()
                self.conn.close()
                return 0, None



    def updateService(self, Info):  #

        id = Info['id']  # 必填，请传递user_id
        serviceName = Info['serviceName']  #
        serviceCode = Info['serviceCode']
        servicePrice = Info['servicePrice']  #
        serviceRate = Info['serviceRate']
        serviceStatus = Info['serviceStatus']
        serviceContent = Info['serviceContent']  #
        serviceContact = Info['serviceContact']  #
        serviceStartDate = Info['serviceStartDate']  #
        serviceEndDate = Info['serviceEndDate']

        cur = self.conn.cursor()
        sql = 'UPDATE zzh_service SET service_name=%s, service_code=%s, service_price=%s, service_rate=%s, service_status=%s,service_content=%s,service_contract=%s,service_startdate=%s,service_enddate=%s   WHERE service_id=%s'
        rs = cur.execute(sql, [serviceName, serviceCode, servicePrice, serviceRate, serviceStatus, serviceContent,
                               serviceContact, serviceStartDate, serviceEndDate, id])
        self.conn.commit()
        cur.close()
        self.conn.close()
        if rs:
            return rs
        else:
            return None

    def deleteService(self, ids):
        """
        根据ID删除数据表信息，ID需要以数组形式传递
        :param adminIdArray: id 构成的数组 []
        :return:
        """
        sql = "delete from zzh_service WHERE service_id=%s"
        cur = self.conn.cursor()
        for id in ids:
            rs = cur.execute(sql, [id])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs
